const mysql = require('mysql2/promise');

async function createChatMessagesTable() {
    let connection;

    try {
        // 数据库连接配置
        const dbConfig = {
            host: 'localhost',
            user: 'root',
            password: '123456',
            database: 'nextjs',
            charset: 'utf8mb4'
        };

        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 检查表是否存在
        const [tables] = await connection.execute(
            "SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema = ? AND table_name = ?",
            ['nextjs', 'chat_messages']
        );

        if (tables[0].count > 0) {
            console.log('📋 chat_messages表已存在，跳过创建');
            return;
        }

        console.log('📋 创建chat_messages表...');

        // 创建聊天消息表
        const createTableSQL = `
      CREATE TABLE IF NOT EXISTS \`chat_messages\` (
        \`id\` int(11) NOT NULL AUTO_INCREMENT COMMENT '消息ID',
        \`content\` text NOT NULL COMMENT '消息内容',
        \`timeDisplay\` varchar(10) NOT NULL COMMENT '时间显示格式 (如: 10:49)',
        \`senderName\` varchar(64) NOT NULL COMMENT '发送者名称',
        \`senderCharacterId\` bigint(20) NOT NULL COMMENT '发送者角色ID',
        \`messageType\` int(11) NOT NULL COMMENT '消息类型 (1:大区, 4:帮派, 5:商业, 6:系统)',
        \`areaId\` bigint(20) DEFAULT NULL COMMENT '区域ID (区域消息专用)',
        \`factionId\` bigint(20) DEFAULT NULL COMMENT '帮派ID (帮派消息专用)',
        \`isActive\` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否有效 (1:有效, 0:已删除)',
        \`priority\` int(11) NOT NULL DEFAULT 0 COMMENT '消息优先级 (0:普通, 1:重要, 2:紧急)',
        \`createdAt\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        \`updatedAt\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (\`id\`),
        KEY \`idx_messageType\` (\`messageType\`),
        KEY \`idx_senderCharacterId\` (\`senderCharacterId\`),
        KEY \`idx_areaId\` (\`areaId\`),
        KEY \`idx_factionId\` (\`factionId\`),
        KEY \`idx_createdAt\` (\`createdAt\`),
        KEY \`idx_isActive\` (\`isActive\`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='聊天消息表'
    `;

        await connection.execute(createTableSQL);
        console.log('✅ chat_messages表创建成功');

        // 插入一些测试数据
        console.log('📝 插入测试数据...');
        const insertTestDataSQL = `
      INSERT INTO \`chat_messages\` 
      (\`content\`, \`timeDisplay\`, \`senderName\`, \`senderCharacterId\`, \`messageType\`, \`areaId\`, \`factionId\`, \`isActive\`, \`priority\`) 
      VALUES 
      ('欢迎来到新手村！', '10:00', '系统', 1000, 6, NULL, NULL, 1, 0),
      ('大家好，我是新手', '10:01', '三国新人', 2041, 1, 1, NULL, 1, 0),
      ('帮派收人，有意者私聊', '10:02', '帮主', 2042, 4, NULL, 1, 1, 0),
      ('出售金币，价格优惠', '10:03', '商人', 2043, 5, NULL, NULL, 1, 0)
    `;

        await connection.execute(insertTestDataSQL);
        console.log('✅ 测试数据插入成功');

        console.log('🎉 chat_messages表创建和初始化完成！');

    } catch (error) {
        console.error('❌ 创建聊天消息表失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行脚本
createChatMessagesTable()
    .then(() => {
        console.log('✅ 脚本执行完成');
        process.exit(0);
    })
    .catch((error) => {
        console.error('❌ 脚本执行失败:', error);
        process.exit(1);
    });
